package com.struts2.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Statement DB类
 * @author Administrator
 * @date 2017年8月2日 上午9:10:15
 * @version v1.0
 */
public class DBUtilStatement {
	
	private final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private final String DB_URL = "jdbc:mysql://localhost:3306/studentsys";
	private final String USER = "root";
	private final String PASS = "123456";
	
	private Connection con = null;
	private Statement state = null;
	private ResultSet result = null;
	/**
	 * 查询学生信息
	 * @author Administrator
	 * @date  2017年8月2日 上午9:10:46
	 * @version v1.0
	 * @param sql
	 * @return
	 */
	public List<Object[]> select(String sql){
		List<Object[]> list = new ArrayList<Object[]>();
		try {
			con=getConnection();
			state = con.createStatement();
		
			result = state.executeQuery(sql);
			ResultSetMetaData metaData = result.getMetaData();
			int columnCount = metaData.getColumnCount();
			
			while(result.next()){
				Object[] objects = new Object[columnCount];
				for (int i = 0; i < objects.length; i++) {
					objects[i]=result.getObject(i+1);
				}
				list.add(objects);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return list;
	}
	/**
	 * 创建连接
	 * @author Administrator
	 * @date  2017年8月2日 上午9:11:11
	 * @version v1.0
	 * @return
	 * @throws Exception
	 */
	public Connection getConnection() throws Exception{
		Class.forName(JDBC_DRIVER);
		return con = DriverManager.getConnection(DB_URL, USER, PASS);
	}
	/**
	 * 增、删、改
	 * @author Administrator
	 * @date  2017年8月2日 上午9:14:51
	 * @version v1.0
	 * @param sql
	 * @return
	 */
	public int opertorIUD(String sql){
		int resultNum = 0;
		try {
			con = getConnection();
			state = con.createStatement();
			
			resultNum = state.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			close();
		}
		return resultNum;
	}
	/**
	 * 关闭
	 * @author Administrator
	 * @date  2017年8月2日 上午9:11:38
	 * @version v1.0
	 */
	public void close() {
		try {
			if(result!=null){
				result.close();
			}
			if(state!=null){
				state.close();
			}
			if(con!=null){
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	public static void main1(String[] args) {
		DBUtilStatement dbUtil = new DBUtilStatement();
		
		/*String sql = "insert into student(id, name, sex, birth, department, address) "
				+ "values(602,'王八','男','1998', '计算机系', '福建省厦门市')";
		int num = dbUtil.opertorIUD(sql);
		System.out.println("受影响行数" + num);*/
		
		/*String sql = "update student set name='王八' where id=602";
		int num = dbUtil.opertorIUD(sql);
		System.out.println("受影响行数" + num);*/
		
		String sql = "delete from student where id=602";
		int num = dbUtil.opertorIUD(sql);
		System.out.println("受影响行数" + num);
		
		/*sql = "select * from student";
		List<Object[]> list = dbUtil.select(sql);
		
		for(Object[] objects : list){
			for(Object obj : objects){
				System.out.print(obj+"\t");
			}
			System.out.println();
		}*/
	}
	
	public ResultSet selectToResultSet(String sql){
		try {
			con=getConnection();
			state = con.createStatement();
			result = state.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
     
	public static void main(String[] args) throws SQLException {
		DBUtilStatement d = new DBUtilStatement();
		ResultSet rs = d.selectToResultSet("select * from admin");
		if(rs!=null){
			while(rs.next()){
				System.out.println(rs.getString(2));
			}
		}
		d.close();
	}
	
}